package com.lanou3g.demo.db;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class DbHelper {

    static {
        dataSource = new ComboPooledDataSource();
    }

    private static ComboPooledDataSource dataSource;

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public static void release() {
        dataSource.close();
    }

    public static Long insert(String sql, Object... params) throws SQLException {
        Connection conn = getConnection();
        Long primaryKey = insert(conn, sql, params);
        conn.close();
        return primaryKey;
    }

    public static Long insert(Connection conn, String sql, Object... params) throws SQLException {
        QueryRunner runner = new QueryRunner();
        return runner.insert(conn, sql, new KeyGenerateHandler(), params);
    }

    public static int update(String sql, Object... params) throws SQLException {
        Connection conn = getConnection();
        int count = update(conn, sql, params);
        conn.close();
        return count;
    }

    public static int update(Connection conn, String sql, Object... params) throws SQLException {
        QueryRunner runner = new QueryRunner();
        return runner.update(conn, sql, params);
    }

    public static <T> T findOne(String sql, Class<T> clazz, Object... params) throws SQLException {
        Connection conn = getConnection();
        T entity = findOne(conn, sql, clazz, params);
        conn.close();
        return entity;
    }

    public static <T> T findOne(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException {
        QueryRunner runner = new QueryRunner();
        return runner.query(conn, sql, new EntityHandler<>(clazz), params);
    }

    public static <T> List<T> findList(String sql, Class<T> clazz, Object... params) throws SQLException {
        Connection conn = getConnection();
        List<T> list = findList(conn, sql, clazz, params);
        conn.close();
        return list;
    }

    public static <T> List<T> findList(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException {
        QueryRunner runner = new QueryRunner();
        return runner.query(conn, sql, new EntityListHandler<>(clazz), params);
    }

    public static <T> T findSingle(Connection conn, String sql, Class<T> clazz, Object... params) throws SQLException {
        QueryRunner runner = new QueryRunner();
        return runner.query(conn, sql, new ScalarHandler<T>(1), params);
    }

    public static <T> T findSingle(String sql, Class<T> clazz, Object... params) throws SQLException {
        Connection conn = getConnection();
        T result = findSingle(conn, sql, clazz, params);
        conn.close();
        return result;
    }

    public static void transaction(OnTransactionHandler handler) throws SQLException {
        Connection conn = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            handler.handle(conn);
            DbUtils.commitAndClose(conn);
        } catch (SQLException e) {
            e.printStackTrace();
            DbUtils.rollbackAndCloseQuietly(conn);
            throw e;
        } finally {
            DbUtils.closeQuietly(conn);
        }
    }

    public interface OnTransactionHandler {
        void handle(Connection conn) throws SQLException;
    }

}
